﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Common;
using Dapper;
using Entity;
using IRepository;
using IService;
using Microsoft.Extensions.Logging;
using Repository;
using XingWeiDaWMSProject.IRepository;

namespace Service
{
    public class InventoryService : IInventoryService
    {
        private readonly IInventoryListRepository _inventoryListRepo;
        private readonly IInventoryDetailsRepository _inventoryDetailsRepo;
        private readonly IWarehouseRepository _warehouseRepo;
        private readonly IProductInformationRepository _productRepo;
        private readonly IInventoryRepository _inventoryRepo;
        private readonly ITargetFormRepository _targetFormTableRepo;
        private readonly DbConnection _dbConnection;
        private readonly INumberingRuleService _numberingRuleService;
        private readonly IOrganizationRepository _orgRepository;
        private readonly ISysOrgUserManagementRepository _userRepo;
        private readonly ILogger<InventoryService> _logger;
        private int? _inventoryTargetId;

        public InventoryService(
            IInventoryListRepository inventoryListRepo,
            IInventoryDetailsRepository inventoryDetailsRepo,
            IWarehouseRepository warehouseRepo,
            IProductInformationRepository productRepo,
            IInventoryRepository inventoryRepo,
            ITargetFormRepository targetFormTableRepo,
            DbConnection dbConnection,
            INumberingRuleService numberingRuleService,
            IOrganizationRepository orgRepository,
            ISysOrgUserManagementRepository userRepo,
            ILogger<InventoryService> logger)
        {
            _inventoryListRepo = inventoryListRepo;
            _inventoryDetailsRepo = inventoryDetailsRepo;
            _warehouseRepo = warehouseRepo;
            _productRepo = productRepo;
            _inventoryRepo = inventoryRepo;
            _targetFormTableRepo = targetFormTableRepo;
            _dbConnection = dbConnection;
            _numberingRuleService = numberingRuleService;
            _orgRepository = orgRepository;
            _userRepo = userRepo;
            _logger = logger;
        }

        private async Task<int> GetInventoryTargetIdAsync()
        {
            if (!_inventoryTargetId.HasValue)
            {
                var targetForm = await _targetFormTableRepo.GetByFormNameAsync("盘点单");
                if (targetForm == null || targetForm.target_id <= 0)
                    throw new Exception("未找到盘点单对应的目标表单ID");
                _inventoryTargetId = targetForm.target_id;
            }
            return _inventoryTargetId.Value;
        }

        public async Task<InventoryListEntity> CreateInventoryListAsync(InventoryListEntity entity, List<InventoryDetailsEntity> details)
        {

            // ✅ 验证Controller传递的状态（应输出1=待审核）
            Console.WriteLine($"===  CreateInventoryListAsync 开始 ===");
            Console.WriteLine($"Service层接收的review_status：{entity.review_status}"); // 关键验证：必须输出1
            Console.WriteLine($"传入的仓库名称: '{entity.warehouse_name}'");

            var allWarehouses = await _warehouseRepo.GetAllAsync();
            Console.WriteLine($"系统中共有 {allWarehouses?.Count ?? 0} 个仓库");

            if (allWarehouses != null && allWarehouses.Any())
            {
                Console.WriteLine("可用仓库列表:");
                foreach (var wh in allWarehouses)
                {
                    Console.WriteLine($"- ID: {wh.warehouse_id}, 名称: '{wh.warehouse_name}', 编号: {wh.warehouse_number}, 状态: {wh.warehouse_status}");
                }
            }

            var warehouse = await _warehouseRepo.GetByNameAsync(entity.warehouse_name?.Trim());
            if (warehouse == null)
            {
                Console.WriteLine($"错误: 未找到名称为 '{entity.warehouse_name}' 的仓库");
                throw new Exception($"仓库 '{entity.warehouse_name}' 不存在，请检查仓库名称是否正确");
            }

            Console.WriteLine($"找到仓库: ID={warehouse.warehouse_id}, 名称='{warehouse.warehouse_name}', 编号={warehouse.warehouse_number}, 状态={warehouse.warehouse_status}");
            Console.WriteLine($"=== 仓库验证通过 ===");

            if (details == null || !details.Any())
                throw new Exception("盘点单明细不能为空，请至少添加一条货品");

            if (!int.TryParse(warehouse.warehouse_status, out int warehouseStatus))
                throw new Exception("仓库状态格式异常");

            var allowedStatus = new List<int> { 1, 2 };
            if (!allowedStatus.Contains(warehouseStatus))
                throw new Exception($"仓库状态异常（当前状态：{warehouseStatus}，允许状态：1-正常、2-锁定状态）");

            var targetId = await GetInventoryTargetIdAsync();
            var ruleId = await Task.Run(() => _numberingRuleService.GetRuleIdByTargetId(targetId));
            if (ruleId <= 0)
                throw new Exception("未配置盘点单编号规则");
            entity.inventory_order_no = await Task.Run(() => _numberingRuleService.GenerateBusinessNumber(ruleId.ToString(), entity.create_by));

            // ❌ 已删除：强制覆盖状态为草稿的致命代码！
            // entity.review_status = InventoryListEntity.STATUS_DRAFT; 

            entity.create_time = DateTime.Now;
            entity.is_delete = "0";

            await _inventoryListRepo.AddAsync(entity);
            Console.WriteLine($"盘点单主表保存成功，inventory_list_id：{entity.inventory_list_id}，最终写入的review_status：{entity.review_status}");

            if (details != null && details.Any())
                await AddInventoryDetailsAsync(entity.inventory_list_id, details);

            // ✅ 状态为1（待审核），自动锁定仓库（无需手动操作）
            if (entity.review_status != InventoryListEntity.STATUS_DRAFT)
            {
                warehouse.warehouse_locked = "1";
                await _warehouseRepo.UpdateAsync(warehouse);
                Console.WriteLine($"仓库 '{warehouse.warehouse_name}' 已锁定（待审核状态强制锁定）");
            }
            else
            {
                warehouse.warehouse_locked = "0";
                await _warehouseRepo.UpdateAsync(warehouse);
                Console.WriteLine($"仓库 '{warehouse.warehouse_name}' 保持未锁定状态（草稿模式）");
            }

            return entity;
        }

        public async Task<InventoryListEntity> GetInventoryListByOrderNoAsync(string orderNo)
        {
            return await _inventoryListRepo.GetByOrderNoAsync(orderNo);
        }

        public async Task UpdateInventoryListStatusAsync(int listId, int status, string reviewer = null)
        {
            var list = await _inventoryListRepo.GetByIdAsync(listId);
            if (list == null)
                throw new Exception("盘点单不存在");

            if (status == InventoryListEntity.STATUS_PENDING_REVIEW && list.review_status != InventoryListEntity.STATUS_DRAFT)
                throw new Exception("只有草稿状态可提交审核");

            if (status >= InventoryListEntity.STATUS_APPROVED_WITH_PROFIT_LOSS)
            {
                if (list.review_status != InventoryListEntity.STATUS_PENDING_REVIEW)
                    throw new Exception("只有待审核状态可审核");

                var details = await _inventoryDetailsRepo.GetByInventoryListIdAsync(listId);
                decimal totalProfitLoss = details.Sum(d => d.pl_num);
                list.review_status = totalProfitLoss != 0m
                    ? InventoryListEntity.STATUS_APPROVED_WITH_PROFIT_LOSS
                    : InventoryListEntity.STATUS_APPROVED_WITHOUT_PROFIT_LOSS;
            }
            else
            {
                list.review_status = status;
            }

            if (status >= InventoryListEntity.STATUS_APPROVED_WITH_PROFIT_LOSS)
            {
                list.reviewer = reviewer;
                list.review_time = DateTime.Now;
            }

            if (status == InventoryListEntity.STATUS_PENDING_REVIEW)
            {
                list.stkt_end_time = DateTime.Now;
                var warehouse = await _warehouseRepo.GetByNameAsync(list.warehouse_name);
                if (warehouse != null)
                {
                    warehouse.warehouse_locked = "1";
                    await _warehouseRepo.UpdateAsync(warehouse);
                }
            }

            await _inventoryListRepo.UpdateAsync(list);
        }

        public async Task CancelInventoryListAsync(int listId)
        {
            var list = await _inventoryListRepo.GetByIdAsync(listId);
            if (list == null)
                throw new Exception("盘点单不存在");

            if (list.review_status != InventoryListEntity.STATUS_DRAFT && list.review_status != InventoryListEntity.STATUS_PENDING_REVIEW)
                throw new Exception("只有草稿或待审核状态可作废");

            list.review_status = InventoryListEntity.STATUS_CANCELLED;
            await _inventoryListRepo.UpdateAsync(list);

            var warehouse = await _warehouseRepo.GetByNameAsync(list.warehouse_name);
            if (warehouse != null)
            {
                warehouse.warehouse_locked = "0";
                await _warehouseRepo.UpdateAsync(warehouse);
            }
        }

        public async Task BatchCancelInventoryListAsync(List<int> listIds)
        {
            if (listIds == null || !listIds.Any())
                throw new ArgumentException("请选择盘点单");

            foreach (var listId in listIds)
                await CancelInventoryListAsync(listId);
        }

        public async Task<PaginationResult<InventoryListEntity>> GetInventoryListAsync(InventoryListQueryParams queryParams)
        {
            var filter = new Dictionary<string, object>();
            if (!string.IsNullOrEmpty(queryParams.OrderNo))
                filter.Add("inventory_order_no", queryParams.OrderNo);
            if (queryParams.ReviewStatus.HasValue)
                filter.Add("review_status", queryParams.ReviewStatus.Value);
            if (!string.IsNullOrEmpty(queryParams.WarehouseName))
                filter.Add("warehouse_name", queryParams.WarehouseName);
            if (!string.IsNullOrEmpty(queryParams.CheckType))
                filter.Add("check_type", queryParams.CheckType);

            var paginationResult = await _inventoryListRepo.GetPagedListAsync(
                queryParams.PageIndex,
                queryParams.PageSize,
                filter,
                "create_time DESC");

            var allOrganizations = await _orgRepository.GetAllNotDeletedAsync();
            var orgDict = allOrganizations.ToDictionary(
                org => org.organization_id,
                org => org.organization_name);

            var handlerIds = paginationResult.Data
                .Where(i => !string.IsNullOrEmpty(i.handler_id))
                .Select(i => i.handler_id)
                .Distinct()
                .ToList();

            var userDict = await _userRepo.GetUserNamesByIdsAsync(handlerIds);

            var resultWithDetails = new List<InventoryListEntity>();
            foreach (var inventory in paginationResult.Data)
            {
                string deptName;
                if (inventory.organizations_id.HasValue && orgDict.TryGetValue(inventory.organizations_id.Value, out deptName))
                {
                    inventory.department_name = deptName;
                }
                else
                {
                    inventory.department_name = "未知部门";
                }

                inventory.handler_name = userDict.TryGetValue(inventory.handler_id, out string userName)
                    ? userName
                    : "未知用户";

                var details = await _inventoryDetailsRepo.GetByInventoryListIdAsync(inventory.inventory_list_id);
                if (details != null && details.Any())
                {
                    // 调试日志：打印查询到的明细数据
                    Console.WriteLine($"=== 明细数据计算开始（单号：{inventory.inventory_order_no}）===");

                    // 🔴 关键修复：通过 product_code 获取产品名称
                    var productCodes = details.Select(d => d.product_code).Distinct().ToList();
                    var productDict = new Dictionary<string, string>();

                    foreach (var productCode in productCodes)
                    {
                        try
                        {
                            var product = await _productRepo.GetByCodeAsync(productCode);
                            if (product != null && !string.IsNullOrEmpty(product.product_name))
                            {
                                productDict[productCode] = product.product_name;
                                Console.WriteLine($"✅ 获取产品名称: {productCode} -> {product.product_name}");
                            }
                            else
                            {
                                productDict[productCode] = productCode; // 使用产品编号作为备选
                                Console.WriteLine($"⚠️ 未找到产品名称，使用编号: {productCode}");
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"❌ 获取产品信息失败 {productCode}: {ex.Message}");
                            productDict[productCode] = productCode; // 使用产品编号作为备选
                        }
                    }

                    // 使用产品字典获取产品名称
                    var productNames = details
                        .Where(d => !string.IsNullOrEmpty(d.product_code) && productDict.ContainsKey(d.product_code))
                        .Select(d => productDict[d.product_code])
                        .Distinct()
                        .ToList();

                    inventory.product_names = productNames.Any()
                        ? string.Join("，", productNames.Take(3)) + (productNames.Count > 3 ? "..." : "")
                        : "无产品信息";

                    Console.WriteLine($"📋 产品名称聚合结果: {inventory.product_names}");

                    // 🔴 核心修正：基于明细重新计算汇总（确保与Controller计算逻辑完全一致）
                    inventory.total_quantity = details.Sum(d => d.stkt_num); // 盘点总数量=明细盘点数量总和

                    // 关键修正：盘盈数量计算（只计算正数）
                    inventory.total_profit_num = details.Where(d => d.pl_num > 0).Sum(d => (int)d.pl_num);

                    // 关键修正：盘亏数量计算（取绝对值）
                    inventory.total_loss_num = details.Where(d => d.pl_num < 0).Sum(d => (int)Math.Abs(d.pl_num));

                    // 关键修正：盘盈金额计算（只计算正数）
                    inventory.total_profit_amount = details.Where(d => d.profit_and_loss_amount > 0).Sum(d => d.profit_and_loss_amount);

                    // 关键修正：盘亏金额计算（取绝对值）
                    inventory.total_loss_amount = details.Where(d => d.profit_and_loss_amount < 0).Sum(d => Math.Abs(d.profit_and_loss_amount));

                    Console.WriteLine($"=== 汇总计算结果（单号：{inventory.inventory_order_no}）===");
                    Console.WriteLine($"  盘点总数量: {inventory.total_quantity}");
                    Console.WriteLine($"  盘盈数量: {inventory.total_profit_num}");
                    Console.WriteLine($"  盘亏数量: {inventory.total_loss_num}");
                    Console.WriteLine($"  盘盈金额: {inventory.total_profit_amount:F2}");
                    Console.WriteLine($"  盘亏金额: {inventory.total_loss_amount:F2}");
                }
                else
                {
                    inventory.product_names = "无明细";
                    inventory.total_quantity = 0;
                    inventory.total_profit_num = 0;
                    inventory.total_loss_num = 0;
                    inventory.total_profit_amount = 0;
                    inventory.total_loss_amount = 0;

                    Console.WriteLine($"=== 无明细数据（单号：{inventory.inventory_order_no}）===");
                }

                resultWithDetails.Add(inventory);
            }

            return new PaginationResult<InventoryListEntity>
            {
                Total = paginationResult.Total,
                Data = resultWithDetails
            };
        }

        public async Task UpdateInventoryListAsync(InventoryListEntity entity, List<InventoryDetailsEntity> details)
        {
            var existingEntity = await _inventoryListRepo.GetByIdAsync(entity.inventory_list_id);
            if (existingEntity == null)
                throw new Exception("盘点单不存在");

            // ✅ 可选：如果需要待审核状态也能修改主表，可修改此处校验（当前保持原逻辑：仅草稿可修改主表）
            if (existingEntity.review_status != InventoryListEntity.STATUS_DRAFT)
                throw new Exception("只有草稿状态可修改盘点单主信息");

            existingEntity.warehouse_name = entity.warehouse_name;
            existingEntity.inventory_start_time = entity.inventory_start_time;
            existingEntity.stkt_end_time = entity.stkt_end_time;
            existingEntity.handler_id = entity.handler_id;
            existingEntity.note = entity.note;
            existingEntity.inventory_types = entity.inventory_types;
            existingEntity.organizations_id = entity.organizations_id;
            await _inventoryListRepo.UpdateAsync(existingEntity);

            await _inventoryDetailsRepo.DeleteByListIdAsync(entity.inventory_list_id);
            if (details != null && details.Any())
                await AddInventoryDetailsAsync(entity.inventory_list_id, details);
        }

        public async Task<System.IO.MemoryStream> ExportInventoryListAsync(InventoryListQueryParams queryParams)
        {
            var result = await GetInventoryListAsync(queryParams);
            if (result == null || !result.Data.Any())
                throw new Exception("无导出数据");

            var stream = new System.IO.MemoryStream();
            var writer = new System.IO.StreamWriter(stream);
            writer.WriteLine("盘点单号,仓库名称,盘点类型,所在部门,开始时间,结束时间,状态,制单人,制单时间,盘盈数量,盘亏数量,盘盈金额,盘亏金额");

            foreach (var item in result.Data)
            {
                writer.WriteLine($"{item.inventory_order_no},{item.warehouse_name},{item.inventory_types}," +
                                 $"{item.department_name},{item.inventory_start_time:yyyy-MM-dd HH:mm}," +
                                 $"{item.stkt_end_time:yyyy-MM-dd HH:mm},{GetStatusText(item.review_status)}," +
                                 $"{item.create_by},{item.create_time:yyyy-MM-dd HH:mm}," +
                                 $"{item.total_profit_num},{item.total_loss_num}," +
                                 $"{item.total_profit_amount:0.00},{item.total_loss_amount:0.00}");
            }

            writer.Flush();
            stream.Position = 0;
            return stream;
        }

        private string GetStatusText(int status)
        {
            return status switch
            {
                InventoryListEntity.STATUS_DRAFT => "草稿",
                InventoryListEntity.STATUS_PENDING_REVIEW => "待审核",
                InventoryListEntity.STATUS_APPROVED_WITH_PROFIT_LOSS => "有盈亏",
                InventoryListEntity.STATUS_APPROVED_WITHOUT_PROFIT_LOSS => "无盈亏",
                InventoryListEntity.STATUS_CANCELLED => "已作废",
                _ => "未知状态"
            };
        }

        public async Task AddInventoryDetailsAsync(int listId, List<InventoryDetailsEntity> details)
        {
            var list = await _inventoryListRepo.GetByIdAsync(listId);
            if (list == null || list.is_delete == "1")
                throw new Exception("盘点单不存在或已删除");

            if (list.review_status != InventoryListEntity.STATUS_DRAFT
                && list.review_status != InventoryListEntity.STATUS_PENDING_REVIEW)
            {
                throw new Exception("只有草稿或待审核状态可添加明细");
            }

            var warehouse = await _warehouseRepo.GetByNameAsync(list.warehouse_name);
            if (warehouse == null)
                throw new Exception("仓库不存在");

            var currentUser = list.create_by;

            Console.WriteLine($"=== 开始添加盘点明细，盘点单ID: {listId}，明细数量: {details.Count} ===");

            foreach (var detail in details)
            {
                detail.inventory_list_id = listId;
                detail.create_time = DateTime.Now;
                detail.update_time = DateTime.Now;
                detail.is_delete = "0";
                detail.inventory_status = 0; 
                detail.create_by = currentUser;
                detail.update_by = currentUser;

                // 获取产品信息（用于验证产品存在性，产品名称已经在Controller层设置）
                var product = await _productRepo.GetByCodeAsync(detail.product_code);
                if (product == null)
                    throw new Exception($"货品编号 {detail.product_code} 不存在，无法添加明细");

                // 验证产品名称是否已设置
                if (string.IsNullOrEmpty(detail.product_name))
                {
                    // 如果Controller层未设置产品名称，这里设置
                    detail.product_name = product.product_name ?? detail.product_code;
                    Console.WriteLine($"⚠️ Controller未设置产品名称，在Service层设置: {detail.product_code} -> {detail.product_name}");
                }

                detail.reference_unit_price = product.stkInrefp;
                detail.book_inventory = detail.book_inventory;
                detail.book_amount = detail.book_inventory * detail.reference_unit_price;
                detail.stkt_num = detail.stkt_num;

                // 关键修正：盈亏计算逻辑
                detail.pl_num = detail.stkt_num - detail.book_inventory;
                detail.profit_and_loss_amount = detail.pl_num * detail.reference_unit_price;

                // 调试日志：打印存储的明细数据
                Console.WriteLine($"📦 明细存储详情：");
                Console.WriteLine($"   产品编号: {detail.product_code}");
                Console.WriteLine($"   产品名称: '{detail.product_name}'");
                Console.WriteLine($"   账面库存: {detail.book_inventory}");
                Console.WriteLine($"   盘点数量: {detail.stkt_num}");
                Console.WriteLine($"   盈亏数量: {detail.pl_num}");
                Console.WriteLine($"   盈亏金额: {detail.profit_and_loss_amount:F2}");
                Console.WriteLine($"   参考单价: {detail.reference_unit_price:F2}");
                Console.WriteLine($"   盘点位置: {detail.inventory_location}");
            }

            await _inventoryDetailsRepo.AddRangeAsync(details);
            Console.WriteLine($"✅ 盘点明细添加完成，共 {details.Count} 条");
        }

        // ✅ 可选修改：如果需要待审核状态也能修改明细，同步修改此方法的校验
        public async Task UpdateInventoryDetailAsync(InventoryDetailsEntity detail)
        {
            var existingDetail = await _inventoryDetailsRepo.GetByIdAsync(detail.inventory_detail_id);
            if (existingDetail == null || existingDetail.is_delete == "1")
                throw new Exception("明细不存在");

            var list = await _inventoryListRepo.GetByIdAsync(existingDetail.inventory_list_id);
            // ✅ 新校验：允许草稿和待审核状态修改明细
            if (list.review_status != InventoryListEntity.STATUS_DRAFT
                && list.review_status != InventoryListEntity.STATUS_PENDING_REVIEW)
            {
                throw new Exception("只有草稿或待审核状态可修改明细");
            }

            existingDetail.stkt_num = detail.stkt_num;
            existingDetail.inventory_location = detail.inventory_location;
            existingDetail.reference_unit_price = detail.reference_unit_price;
            existingDetail.note = detail.note;

            existingDetail.pl_num = existingDetail.stkt_num - existingDetail.book_inventory;
            existingDetail.profit_and_loss_amount = existingDetail.pl_num * existingDetail.reference_unit_price;

            await _inventoryDetailsRepo.UpdateAsync(existingDetail);
        }

        public async Task<List<InventoryDetailsEntity>> GetInventoryDetailsByListIdAsync(int listId)
        {
            return await _inventoryDetailsRepo.GetByInventoryListIdAsync(listId);
        }

        public async Task AdjustInventoryAsync(int listId)
        {
            var list = await _inventoryListRepo.GetByIdAsync(listId);
            if (list == null || list.is_delete == "1")
                throw new Exception("盘点单不存在");
            if (list.review_status != InventoryListEntity.STATUS_APPROVED_WITH_PROFIT_LOSS
                && list.review_status != InventoryListEntity.STATUS_APPROVED_WITHOUT_PROFIT_LOSS)
                throw new Exception("只有已审核状态可调整库存");

            var warehouse = await _warehouseRepo.GetByNameAsync(list.warehouse_name);
            if (warehouse == null)
                throw new Exception("仓库不存在");

            var details = await _inventoryDetailsRepo.GetByInventoryListIdAsync(listId);
            if (!details.Any())
                throw new Exception("无明细数据");

            bool wasConnectionOpen = _dbConnection.State == ConnectionState.Open;
            if (!wasConnectionOpen)
                _dbConnection.Open();

            using var transaction = _dbConnection.BeginTransaction();
            try
            {
                foreach (var detail in details)
                {
                    var inventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        warehouse.warehouse_id, detail.product_code, transaction);

                    if (inventories.Any())
                    {
                        foreach (var inventory in inventories)
                        {
                            inventory.current_inventory += (int)detail.pl_num;
                            inventory.warehouse_code = warehouse.warehouse_number;
                            await _inventoryRepo.UpdateAsync(inventory, transaction);
                        }
                    }
                    else if (detail.pl_num > 0)
                    {
                        var newInventory = new InventoryEntity
                        {
                            warehouse_id = warehouse.warehouse_id,
                            warehouse_code = warehouse.warehouse_number,
                            product_code = detail.product_code,
                            current_inventory = (int)detail.stkt_num,
                            planned_outgoing_quantity = 0
                        };
                        await _inventoryRepo.AddAsync(newInventory, transaction);
                    }
                }

                warehouse.warehouse_locked = "1";
                await _warehouseRepo.UpdateAsync(warehouse, transaction);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception("库存调整失败：" + ex.Message);
            }
            finally
            {
                if (!wasConnectionOpen && _dbConnection.State == ConnectionState.Open)
                    _dbConnection.Close();
            }
        }

        public async Task<InventoryListEntity> GetInventoryListByIdAsync(int id)
        {
            var entity = await _inventoryListRepo.GetByIdAsync(id);
            if (entity == null)
                throw new Exception($"未找到ID为 {id} 的盘点单");

            var allOrganizations = await _orgRepository.GetAllNotDeletedAsync();
            var orgDict = allOrganizations.ToDictionary(o => o.organization_id, o => o.organization_name);
            string deptName;
            if (entity.organizations_id.HasValue && orgDict.TryGetValue(entity.organizations_id.Value, out deptName))
            {
                entity.department_name = deptName;
            }
            else
            {
                entity.department_name = "未知部门";
            }

            if (!string.IsNullOrEmpty(entity.handler_id))
            {
                var userDict = await _userRepo.GetUserNamesByIdsAsync(new List<string> { entity.handler_id });
                entity.handler_name = userDict.TryGetValue(entity.handler_id, out string userName)
                    ? userName
                    : "未知用户";
            }
            else
            {
                entity.handler_name = "无经办人";
            }

            var details = await _inventoryDetailsRepo.GetByInventoryListIdAsync(entity.inventory_list_id);
            if (details != null && details.Any())
            {
                // 🔴 关键修复：通过 product_code 获取产品名称
                var productCodes = details.Select(d => d.product_code).Distinct().ToList();
                var productDict = new Dictionary<string, string>();

                foreach (var productCode in productCodes)
                {
                    try
                    {
                        var product = await _productRepo.GetByCodeAsync(productCode);
                        if (product != null && !string.IsNullOrEmpty(product.product_name))
                        {
                            productDict[productCode] = product.product_name;
                        }
                        else
                        {
                            productDict[productCode] = productCode; // 使用产品编号作为备选
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"❌ 获取产品信息失败 {productCode}: {ex.Message}");
                        productDict[productCode] = productCode; // 使用产品编号作为备选
                    }
                }

                // 使用产品字典获取产品名称
                var productNames = details
                    .Where(d => !string.IsNullOrEmpty(d.product_code) && productDict.ContainsKey(d.product_code))
                    .Select(d => productDict[d.product_code])
                    .Distinct()
                    .ToList();

                entity.product_names = productNames.Any()
                    ? string.Join("，", productNames.Take(3)) + (productNames.Count > 3 ? "..." : "")
                    : "无产品信息";

                // 关键修正：确保单个盘点单的盈亏计算也正确
                entity.total_profit_num = details.Where(d => d.pl_num > 0).Sum(d => (int)d.pl_num);
                entity.total_loss_num = details.Where(d => d.pl_num < 0).Sum(d => (int)Math.Abs(d.pl_num));
                entity.total_profit_amount = details.Where(d => d.profit_and_loss_amount > 0).Sum(d => d.profit_and_loss_amount);
                entity.total_loss_amount = details.Where(d => d.profit_and_loss_amount < 0).Sum(d => Math.Abs(d.profit_and_loss_amount));

                Console.WriteLine($"=== 单个盘点单计算结果（ID：{id}）===");
                Console.WriteLine($"  产品名称: {entity.product_names}");
                Console.WriteLine($"  盘盈数量: {entity.total_profit_num}");
                Console.WriteLine($"  盘亏数量: {entity.total_loss_num}");
                Console.WriteLine($"  盘盈金额: {entity.total_profit_amount:F2}");
                Console.WriteLine($"  盘亏金额: {entity.total_loss_amount:F2}");
            }
            else
            {
                entity.product_names = "无明细";
                entity.total_profit_num = 0;
                entity.total_loss_num = 0;
                entity.total_profit_amount = 0;
                entity.total_loss_amount = 0;
            }

            return entity;
        }

        public async Task LockWarehouseAsync(string warehouseName)
        {
            var warehouse = await _warehouseRepo.GetByNameAsync(warehouseName);
            if (warehouse == null)
                throw new Exception($"仓库 '{warehouseName}' 不存在");

            if (warehouse.warehouse_locked == "1")
                throw new Exception($"仓库 '{warehouseName}' 已锁定");

            var ongoingInventory = await _inventoryListRepo.GetOngoingInventoryByWarehouseAsync(warehouseName);
            if (ongoingInventory != null)
                throw new Exception($"仓库 '{warehouseName}' 有进行中的盘点单");

            await _inventoryRepo.LockWarehouseAsync(warehouseName);
        }

        public async Task<List<WarehouseEntity>> GetAllWarehousesAsync()
        {
            return await _warehouseRepo.GetAllAsync();
        }

        public async Task DeleteInventoryDetailAsync(int detailId)
        {
            await _inventoryRepo.DeleteInventoryDetailAsync(detailId);
        }

        public async Task DeleteInventoryListAsync(int listId)
        {
            await _inventoryDetailsRepo.DeleteByListIdAsync(listId);
            await _inventoryListRepo.DeleteByListIdAsync(listId);
        }

        public async Task BatchDeleteInventoryListAsync(List<int> listIds)
        {
            foreach (var id in listIds)
            {
                await DeleteInventoryListAsync(id);
            }
        }

        public async Task<bool> CheckWarehouseLockAsync(string warehouseName)
        {
            return await _inventoryRepo.CheckWarehouseLockAsync(warehouseName);
        }

        public async Task<List<StorageLocationEntity>> GetAllStorageLocationsAsync()
        {
            return await _warehouseRepo.GetAllStorageLocationsAsync();
        }

        public async Task<List<StorageLocationEntity>> GetStorageLocationsByWarehouseAsync(string warehouseName)
        {
            Console.WriteLine($"🔄 Service层：开始获取仓库 '{warehouseName}' 的库位");

            var locations = await _warehouseRepo.GetStorageLocationsByWarehouseAsync(warehouseName);

            Console.WriteLine($"✅ Service层：获取到 {locations?.Count ?? 0} 个库位");
            if (locations != null)
            {
                foreach (var location in locations)
                {
                    Console.WriteLine($"   库位: {location.location_name}, 仓库: {location.warehouse_name}");
                }
            }

            return locations ?? new List<StorageLocationEntity>();
        }

        public async Task<bool> AdjustInventoryForTransfer(int outWarehouseId, int inWarehouseId, List<TransferOrderDetailDTO> details)
        {
            IDbTransaction transaction = null;
            bool wasConnectionOpen = false;

            try
            {
                var outWarehouse = await _warehouseRepo.GetByIdAsync(outWarehouseId);
                if (outWarehouse == null)
                    throw new Exception($"调出仓库ID {outWarehouseId} 不存在");

                var inWarehouse = await _warehouseRepo.GetByIdAsync(inWarehouseId);
                if (inWarehouse == null)
                    throw new Exception($"调入仓库ID {inWarehouseId} 不存在");

                wasConnectionOpen = _dbConnection.State == ConnectionState.Open;
                if (!wasConnectionOpen)
                    _dbConnection.Open();

                transaction = _dbConnection.BeginTransaction();

                foreach (var detail in details)
                {
                    var outInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        outWarehouseId, detail.product_code, transaction);

                    if (outInventories == null || !outInventories.Any())
                        throw new Exception($"调出仓库中未找到产品{detail.product_code}的库存记录");

                    int remainingQuantity = detail.transfer_quantity;

                    foreach (var inventory in outInventories)
                    {
                        if (remainingQuantity <= 0) break;

                        if (inventory.current_inventory < remainingQuantity)
                        {
                            remainingQuantity -= inventory.current_inventory;
                            inventory.current_inventory = 0;
                        }
                        else
                        {
                            inventory.current_inventory -= remainingQuantity;
                            remainingQuantity = 0;
                        }

                        inventory.warehouse_code = outWarehouse.warehouse_number;
                        await _inventoryRepo.UpdateAsync(inventory, transaction);
                    }

                    if (remainingQuantity > 0)
                        throw new Exception($"调出仓库中产品{detail.product_code}的库存不足");

                    var inInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        inWarehouseId, detail.product_code, transaction);

                    if (inInventories != null && inInventories.Any())
                    {
                        var firstInventory = inInventories.First();
                        firstInventory.current_inventory += detail.transfer_quantity;
                        firstInventory.warehouse_code = inWarehouse.warehouse_number;
                        await _inventoryRepo.UpdateAsync(firstInventory, transaction);
                    }
                    else
                    {
                        var storageLocation = await _warehouseRepo.GetFirstValidStorageLocationAsync(inWarehouseId, transaction);
                        if (storageLocation == null)
                        {
                            throw new Exception($"调入仓库（ID：{inWarehouseId}）无可用货位，请先配置货位");
                        }

                        await _inventoryRepo.AddAsync(new InventoryEntity
                        {
                            warehouse_id = inWarehouseId,
                            warehouse_code = inWarehouse.warehouse_number,
                            product_code = detail.product_code,
                            current_inventory = detail.transfer_quantity,
                            planned_outgoing_quantity = 0,
                            location = storageLocation.location_name
                        }, transaction);
                    }
                }

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                if (transaction != null)
                    transaction.Rollback();
                _logger.LogError(ex, "调拨单库存调整失败");
                return false;
            }
            finally
            {
                transaction?.Dispose();
                if (!wasConnectionOpen && _dbConnection.State == ConnectionState.Open)
                    _dbConnection.Close();
            }
        }

        /// <summary>
        /// 调拨单撤销：库存回滚
        /// </summary>
        public async Task<bool> RollbackInventoryForTransfer(
            int outWarehouseId,
            int inWarehouseId,
            List<TransferOrderDetailDTO> details)
        {
            if (outWarehouseId <= 0) throw new ArgumentException("调出仓库ID无效");
            if (inWarehouseId <= 0) throw new ArgumentException("调入仓库ID无效");
            if (details == null || details.Count == 0) throw new ArgumentException("调拨明细不能为空");

            IDbTransaction transaction = null;
            bool wasOpen = false;

            try
            {
                wasOpen = _dbConnection.State == ConnectionState.Open;
                if (!wasOpen) _dbConnection.Open();
                transaction = _dbConnection.BeginTransaction();

                foreach (var detail in details)
                {
                    // 1. 调出仓库：恢复库存（增加）
                    var outInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        outWarehouseId, detail.product_code, transaction);

                    if (outInventories == null || !outInventories.Any())
                    {
                        // 修正：移除UpdateTime赋值（InventoryEntity无此属性）
                        await _inventoryRepo.AddAsync(new InventoryEntity
                        {
                            warehouse_id = outWarehouseId,
                            product_code = detail.product_code,
                            current_inventory = detail.transfer_quantity,
                        }, transaction);
                    }
                    else
                    {
                        var firstOutInventory = outInventories.First();
                        firstOutInventory.current_inventory += detail.transfer_quantity;
                        // 修正：移除UpdateTime赋值（InventoryEntity无此属性）
                        await _inventoryRepo.UpdateAsync(firstOutInventory, transaction);
                    }

                    // 2. 调入仓库：扣减库存（减少）
                    var inInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        inWarehouseId, detail.product_code, transaction);

                    if (inInventories == null || !inInventories.Any())
                        throw new Exception($"调入仓库{inWarehouseId}无货品{detail.product_code}的库存记录");

                    var firstInInventory = inInventories.First();
                    if (firstInInventory.current_inventory < detail.transfer_quantity)
                        throw new Exception($"调入仓库{inWarehouseId}库存不足（需扣减{detail.transfer_quantity}，当前{firstInInventory.current_inventory}）");

                    firstInInventory.current_inventory -= detail.transfer_quantity;
                    // 修正：移除UpdateTime赋值（InventoryEntity无此属性）
                    await _inventoryRepo.UpdateAsync(firstInInventory, transaction);
                }

                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();
                _logger.LogError(ex, "库存回滚失败");
                return false;
            }
            finally
            {
                transaction?.Dispose();
                if (!wasOpen && _dbConnection.State == ConnectionState.Open)
                    _dbConnection.Close();
            }
        }
        /// <summary>
        /// 带事务的调拨库存调整（修正后，匹配接口）
        /// </summary>
        public async Task<bool> AdjustInventoryForTransfer(
            int outWarehouseId,
            int inWarehouseId,
            List<TransferOrderDetailDTO> details,
            DbTransaction transaction) // 改为 DbTransaction，与接口一致
        {
            try
            {
                // 查询调出仓库（传递 DbTransaction）
                var outWarehouse = await _warehouseRepo.GetByIdAsync(outWarehouseId, transaction);
                if (outWarehouse == null)
                    throw new Exception($"调出仓库ID {outWarehouseId} 不存在");

                // 查询调入仓库（传递 DbTransaction）
                var inWarehouse = await _warehouseRepo.GetByIdAsync(inWarehouseId, transaction);
                if (inWarehouse == null)
                    throw new Exception($"调入仓库ID {inWarehouseId} 不存在");

                foreach (var detail in details)
                {
                    // 调出库存查询（传递 DbTransaction）
                    var outInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        outWarehouseId, detail.product_code, transaction);

                    if (outInventories == null || !outInventories.Any())
                        throw new Exception($"调出仓库中未找到产品{detail.product_code}的库存记录");

                    int remainingQuantity = detail.transfer_quantity;

                    foreach (var inventory in outInventories)
                    {
                        if (remainingQuantity <= 0) break;

                        if (inventory.current_inventory < remainingQuantity)
                        {
                            remainingQuantity -= inventory.current_inventory;
                            inventory.current_inventory = 0;
                        }
                        else
                        {
                            inventory.current_inventory -= remainingQuantity;
                            remainingQuantity = 0;
                        }

                        inventory.warehouse_code = outWarehouse.warehouse_number;
                        await _inventoryRepo.UpdateAsync(inventory, transaction); // 传递 DbTransaction
                    }

                    if (remainingQuantity > 0)
                        throw new Exception($"调出仓库中产品{detail.product_code}的库存不足");

                    // 调入库存查询（传递 DbTransaction）
                    var inInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        inWarehouseId, detail.product_code, transaction);

                    if (inInventories != null && inInventories.Any())
                    {
                        var firstInventory = inInventories.First();
                        firstInventory.current_inventory += detail.transfer_quantity;
                        firstInventory.warehouse_code = inWarehouse.warehouse_number;
                        await _inventoryRepo.UpdateAsync(firstInventory, transaction); // 传递 DbTransaction
                    }
                    else
                    {
                        // 货位查询（传递 DbTransaction）
                        var storageLocation = await _warehouseRepo.GetFirstValidStorageLocationAsync(
                            inWarehouseId, transaction);
                        if (storageLocation == null)
                        {
                            throw new Exception($"调入仓库（ID：{inWarehouseId}）无可用货位，请先配置货位");
                        }

                        await _inventoryRepo.AddAsync(new InventoryEntity
                        {
                            warehouse_id = inWarehouseId,
                            warehouse_code = inWarehouse.warehouse_number,
                            product_code = detail.product_code,
                            current_inventory = detail.transfer_quantity,
                            planned_outgoing_quantity = 0,
                            location = storageLocation.location_name
                        }, transaction); // 传递 DbTransaction
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "带事务的调拨单库存调整失败");
                return false;
            }
        }

        /// <summary>
        /// 带事务的调拨库存回滚（实现接口方法）
        /// </summary>
        public async Task<bool> RollbackInventoryForTransfer(
            int outWarehouseId,
            int inWarehouseId,
            List<TransferOrderDetailDTO> details,
            DbTransaction transaction)
        {
            if (outWarehouseId <= 0) throw new ArgumentException("调出仓库ID无效");
            if (inWarehouseId <= 0) throw new ArgumentException("调入仓库ID无效");
            if (details == null || details.Count == 0) throw new ArgumentException("调拨明细不能为空");

            try
            {
                // 直接使用外部传入的事务（不再创建新事务）
                foreach (var detail in details)
                {
                    // 1. 调出仓库：恢复库存（增加）
                    var outInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        outWarehouseId, detail.product_code, transaction); // 传递事务

                    if (outInventories == null || !outInventories.Any())
                    {
                        await _inventoryRepo.AddAsync(new InventoryEntity
                        {
                            warehouse_id = outWarehouseId,
                            product_code = detail.product_code,
                            current_inventory = detail.transfer_quantity,
                        }, transaction); // 传递事务
                    }
                    else
                    {
                        var firstOutInventory = outInventories.First();
                        firstOutInventory.current_inventory += detail.transfer_quantity;
                        await _inventoryRepo.UpdateAsync(firstOutInventory, transaction); // 传递事务
                    }

                    // 2. 调入仓库：扣减库存（减少）
                    var inInventories = await _inventoryRepo.GetByWarehouseAndProductAsync(
                        inWarehouseId, detail.product_code, transaction); // 传递事务

                    if (inInventories == null || !inInventories.Any())
                        throw new Exception($"调入仓库{inWarehouseId}无货品{detail.product_code}的库存记录");

                    var firstInInventory = inInventories.First();
                    if (firstInInventory.current_inventory < detail.transfer_quantity)
                        throw new Exception($"调入仓库{inWarehouseId}库存不足（需扣减{detail.transfer_quantity}，当前{firstInInventory.current_inventory}）");

                    firstInInventory.current_inventory -= detail.transfer_quantity;
                    await _inventoryRepo.UpdateAsync(firstInInventory, transaction); // 传递事务
                }

                return true; // 事务由外部提交/回滚，此处不处理
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "带事务的库存回滚失败");
                return false;
            }
        }
        /// <summary>
        /// 审核盘点单并更新库存
        /// </summary>
        public async Task ApproveInventoryAsync(int inventoryId, string reviewer)
        {
            Console.WriteLine($"=== 开始审核盘点单，ID: {inventoryId}，审核人: {reviewer} ===");

            bool wasConnectionOpen = _dbConnection.State == ConnectionState.Open;
            if (!wasConnectionOpen)
            {
                Console.WriteLine("数据库连接关闭，重新打开连接...");
                await _dbConnection.OpenAsync();
            }

            using (var transaction = await _dbConnection.BeginTransactionAsync())
            {
                try
                {
                    Console.WriteLine("事务开始成功");

                    // 1. 获取盘点单信息
                    var inventory = await _dbConnection.QueryFirstOrDefaultAsync<InventoryListEntity>(
                        "SELECT * FROM wms_knzy_inventory_list WHERE inventory_list_id = @Id AND is_delete = 0",
                        new { Id = inventoryId }, transaction);

                    if (inventory == null)
                        throw new Exception($"未找到ID为{inventoryId}的盘点单");

                    Console.WriteLine($"找到盘点单：单号={inventory.inventory_order_no}，仓库={inventory.warehouse_name}，状态={inventory.review_status}");

                    // 2. 检查当前状态是否为待审核
                    if (inventory.review_status != 1)
                        throw new Exception("只有待审核状态的盘点单才能进行审核");

                    // 3. 通过仓库名称查询仓库实体
                    var warehouse = await _dbConnection.QueryFirstOrDefaultAsync<WarehouseEntity>(
                        "SELECT * FROM wms_basic_warehouse WHERE warehouse_name = @WarehouseName AND is_delete = 0",
                        new { WarehouseName = inventory.warehouse_name }, transaction);

                    if (warehouse == null)
                        throw new Exception($"未找到仓库：{inventory.warehouse_name}");

                    Console.WriteLine($"找到仓库：ID={warehouse.warehouse_id}，名称={warehouse.warehouse_name}，编号={warehouse.warehouse_number}");

                    // 4. 获取盘点明细 - 修正：只查询实际存在的字段
                    var details = await _dbConnection.QueryAsync<InventoryDetailsEntity>(
                        "SELECT inventory_detail_id, inventory_list_id, product_code, inventory_location, " +
                        "book_inventory, stkt_num, pl_num, profit_and_loss_amount, note, reference_unit_price " +
                        "FROM wms_knzy_inventory_details WHERE inventory_list_id = @ListId AND is_delete = 0",
                        new { ListId = inventoryId }, transaction);

                    var detailsList = details.AsList();
                    if (!detailsList.Any())
                        throw new Exception("该盘点单没有明细数据");

                    Console.WriteLine($"找到{detailsList.Count}条盘点明细：");
                    foreach (var detail in detailsList)
                    {
                        Console.WriteLine($"  - 产品：'{detail.product_code}'，库位：'{detail.inventory_location}'，" +
                                        $"账面库存：{detail.book_inventory}，盘点数量：{detail.stkt_num}，盈亏：{detail.pl_num}");
                    }

                    // 5. 根据盘点结果更新 wms_knzy_inventory 表
                    foreach (var detail in detailsList)
                    {
                        Console.WriteLine($"=== 开始处理产品：'{detail.product_code}' ===");
                        Console.WriteLine($"库位：'{detail.inventory_location}'，账面库存：{detail.book_inventory}，盘点数量：{detail.stkt_num}，盈亏：{detail.pl_num}");

                        // 检查库位是否为空
                        if (string.IsNullOrEmpty(detail.inventory_location))
                        {
                            throw new Exception($"产品 '{detail.product_code}' 的盘点库位不能为空");
                        }

                        // 关键修复：处理产品编码格式问题
                        string productCodeForSearch = detail.product_code?.Replace(" ", ""); // 移除空格
                        Console.WriteLine($"产品编码处理：'{detail.product_code}' -> '{productCodeForSearch}'");

                        // 检查该产品在指定库位是否已有库存记录
                        var existingInventory = await _dbConnection.QueryFirstOrDefaultAsync<InventoryEntity>(
                            @"SELECT * FROM wms_knzy_inventory 
  WHERE warehouse_id = @WarehouseId 
    AND product_code = @ProductCode 
    AND location = @Location 
    AND is_delete = 0",
                            new
                            {
                                WarehouseId = warehouse.warehouse_id,
                                ProductCode = productCodeForSearch,
                                Location = detail.inventory_location
                            }, transaction);

                        if (existingInventory != null)
                        {
                            // 🔴 关键问题：这里应该使用 detail.stkt_num（盘点数量），而不是 detail.stkt_num
                            Console.WriteLine($"找到现有库存记录：");
                            Console.WriteLine($"  - 库存ID：{existingInventory.inventory_id}");
                            Console.WriteLine($"  - 原库存：{existingInventory.current_inventory}");
                            Console.WriteLine($"  - 新库存：{detail.stkt_num}"); // 这里应该是盘点数量
                            Console.WriteLine($"  - 仓库ID：{warehouse.warehouse_id}");
                            Console.WriteLine($"  - 产品编码：'{productCodeForSearch}'");
                            Console.WriteLine($"  - 库位：'{detail.inventory_location}'");

                            // 🔴 关键修复：确保使用盘点数量更新库存
                            int rowsAffected = await _dbConnection.ExecuteAsync(
                                @"UPDATE wms_knzy_inventory SET 
      current_inventory = @CurrentInventory,
      warehouse_code = @WarehouseCode
    WHERE inventory_id = @InventoryId",
                                new
                                {
                                    CurrentInventory = (int)detail.stkt_num, // 🔴 这里必须是盘点数量
                                    WarehouseCode = warehouse.warehouse_number,
                                    InventoryId = existingInventory.inventory_id
                                }, transaction);

                            Console.WriteLine($"更新影响行数：{rowsAffected}");

                            if (rowsAffected == 0)
                            {
                                throw new Exception($"更新库存失败，产品：'{detail.product_code}'");
                            }
                        }
                        else
                        {
                            // 创建新库存记录
                            Console.WriteLine($"未找到现有库存记录，创建新记录：");
                            Console.WriteLine($"  - 仓库ID：{warehouse.warehouse_id}");
                            Console.WriteLine($"  - 产品编码：'{productCodeForSearch}'");
                            Console.WriteLine($"  - 库位：'{detail.inventory_location}'");
                            Console.WriteLine($"  - 库存数量：{detail.stkt_num}"); // 🔴 使用盘点数量

                            int rowsAffected = await _dbConnection.ExecuteAsync(
                                @"INSERT INTO wms_knzy_inventory (
        warehouse_id, warehouse_code, location,
        product_code, current_inventory, planned_outgoing_quantity, is_delete
    ) VALUES (
        @WarehouseId, @WarehouseCode, @Location,
        @ProductCode, @CurrentInventory, 0, 0
    )",
                                new
                                {
                                    WarehouseId = warehouse.warehouse_id,
                                    WarehouseCode = warehouse.warehouse_number,
                                    Location = detail.inventory_location,
                                    ProductCode = productCodeForSearch,
                                    CurrentInventory = (int)detail.stkt_num // 🔴 使用盘点数量
                                }, transaction);

                            Console.WriteLine($"插入影响行数：{rowsAffected}");
                        }

                        Console.WriteLine($"✅ 库存处理完成：产品 '{detail.product_code}'");
                    }

                    // 6. 更新盘点单状态
                    var hasProfitOrLoss = detailsList.Any(d => d.pl_num != 0);
                    var newStatus = hasProfitOrLoss ? 2 : 3; // 2=有盈亏，3=无盈亏

                    Console.WriteLine($"更新盘点单状态：{inventory.review_status} → {newStatus}，是否有盈亏：{hasProfitOrLoss}");

                    int statusRows = await _dbConnection.ExecuteAsync(
                        @"UPDATE wms_knzy_inventory_list SET
                  review_status = @ReviewStatus,
                  reviewer = @Reviewer,
                  review_time = GETDATE(),
                  update_by = @UpdateBy,
                  update_time = GETDATE()
                WHERE inventory_list_id = @InventoryListId",
                        new
                        {
                            ReviewStatus = newStatus,
                            Reviewer = reviewer,
                            UpdateBy = reviewer,
                            InventoryListId = inventoryId
                        }, transaction);

                    Console.WriteLine($"状态更新影响行数：{statusRows}");

                    // 7. 解锁仓库
                    int warehouseRows = await _dbConnection.ExecuteAsync(
                        "UPDATE wms_basic_warehouse SET warehouse_locked = '0' WHERE warehouse_id = @WarehouseId",
                        new { WarehouseId = warehouse.warehouse_id }, transaction);

                    Console.WriteLine($"仓库解锁影响行数：{warehouseRows}");

                    await transaction.CommitAsync();
                    Console.WriteLine($"✅ 事务提交成功，审核完成 ID: {inventoryId}");

                    // 8. 输出审核结果摘要
                    Console.WriteLine($"=== 审核结果摘要 ===");
                    Console.WriteLine($"盘点单号：{inventory.inventory_order_no}");
                    Console.WriteLine($"仓库：{inventory.warehouse_name}");
                    Console.WriteLine($"审核人：{reviewer}");
                    Console.WriteLine($"最终状态：{(newStatus == 2 ? "有盈亏" : "无盈亏")}");
                    Console.WriteLine($"处理明细数量：{detailsList.Count}");
                    foreach (var detail in detailsList)
                    {
                        Console.WriteLine($"  - {detail.product_code}: {detail.book_inventory} → {detail.stkt_num} ({(detail.pl_num > 0 ? "盘盈" : detail.pl_num < 0 ? "盘亏" : "无盈亏")})");
                    }
                }
                catch (Exception ex)
                {
                    await transaction.RollbackAsync();
                    Console.WriteLine($"❌ 审核异常：{ex.Message}\n堆栈：{ex.StackTrace}");
                    throw new Exception($"审核失败：{ex.Message}");
                }
                finally
                {
                    if (!wasConnectionOpen && _dbConnection.State == ConnectionState.Open)
                    {
                        _dbConnection.Close();
                        Console.WriteLine("数据库连接已关闭");
                    }
                }
            }
        }
       
        public async Task<int> GetCurrentStockByProductCodeAsync(string productCode)
        {
            // 调用现有的方法，传递 null 作为仓库名称
            return await GetCurrentStockByProductCodeAsync(productCode, null);
        }
        public async Task<int> GetCurrentStockByProductCodeAsync(string productCode, string warehouseName = null)
        {
            try
            {
                Console.WriteLine($"🔍 详细库存查询 - 产品: {productCode}, 仓库: {warehouseName}");

                if (string.IsNullOrEmpty(warehouseName))
                {
                    Console.WriteLine($"⚠️ 未指定仓库名称");
                    return 0;
                }

                // 根据仓库名称查询仓库信息
                var warehouse = await _warehouseRepo.GetByNameAsync(warehouseName);
                if (warehouse == null)
                {
                    Console.WriteLine($"❌ 未找到仓库: {warehouseName}");
                    return 0;
                }

                Console.WriteLine($"✅ 找到仓库: ID={warehouse.warehouse_id}, 名称={warehouse.warehouse_name}");

                // 详细调试：查询所有相关库存记录
                var allStocks = await _inventoryRepo.GetByWarehouseAndProductAsync(warehouse.warehouse_id, productCode);
                Console.WriteLine($"📊 详细库存记录查询:");
                foreach (var stock in allStocks)
                {
                    Console.WriteLine($"  - 库存ID: {stock.inventory_id}, 库位: '{stock.location}', 数量: {stock.current_inventory}");
                }

                // 使用批量查询方法
                var stockDict = await _inventoryRepo.GetBatchStockByProductCodesAsync(
                    new List<string> { productCode },
                    warehouse.warehouse_id);

                Console.WriteLine($"📊 批量查询结果: {string.Join(", ", stockDict.Select(kv => $"{kv.Key}={kv.Value}"))}");

                if (stockDict.TryGetValue(productCode, out int currentStock))
                {
                    Console.WriteLine($"✅ 最终库存结果: {productCode} = {currentStock}");
                    return currentStock;
                }
                else
                {
                    Console.WriteLine($"⚠️ 产品 {productCode} 在仓库 {warehouse.warehouse_name} 中无库存记录");
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询货品 {productCode} 库存异常: {ex.Message}");
                return 0;
            }
        }
        public async Task<int> GetCurrentStockByProductAndLocationAsync(string productCode, string warehouseName, string locationName)
        {
            try
            {
                Console.WriteLine($"🔍 按库位查询库存 - 产品: {productCode}, 仓库: {warehouseName}, 库位: {locationName}");

                if (string.IsNullOrEmpty(warehouseName) || string.IsNullOrEmpty(locationName))
                {
                    Console.WriteLine($"⚠️ 仓库或库位名称为空");
                    return 0;
                }

                // 根据仓库名称查询仓库信息
                var warehouse = await _warehouseRepo.GetByNameAsync(warehouseName);
                if (warehouse == null)
                {
                    Console.WriteLine($"❌ 未找到仓库: {warehouseName}");
                    return 0;
                }

                // 查询指定库位的库存
                var sql = @"SELECT current_inventory 
            FROM wms_knzy_inventory 
            WHERE warehouse_id = @WarehouseId 
                AND product_code = @ProductCode 
                AND location = @Location 
                AND is_delete = 0";

                var stock = await _dbConnection.QueryFirstOrDefaultAsync<int?>(sql, new
                {
                    WarehouseId = warehouse.warehouse_id,
                    ProductCode = productCode,
                    Location = locationName
                });

                if (stock.HasValue)
                {
                    Console.WriteLine($"✅ 按库位查询结果: {productCode} 在 {locationName} 的库存 = {stock.Value}");
                    return stock.Value;
                }
                else
                {
                    Console.WriteLine($"⚠️ 产品 {productCode} 在库位 {locationName} 中无库存记录");
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 按库位查询库存异常: {ex.Message}");
                return 0;
            }
        }
    }
}
